from app.models import Content, User,Task
from app.extensions import db
from math import ceil
from sqlalchemy import text
from datetime import date,datetime
class ContentService:
    """学习内容管理服务"""

    @staticmethod
    def delete_contents(content_ids):
        for content_id in content_ids:
            content = Content.query.get(content_id)
            if not content:
                return {'code': 404, 'msg': '内容不存在'}
            db.session.delete(content)
        db.session.commit()
        return {'code': 200, 'msg': '内容删除成功'}

    @staticmethod
    def create_content(creator_id: int, content_type: str, title: str, text: str, 
                      difficulty: str, pinyin: str = None, translation: str = None) -> dict:
        """创建学习内容"""
        creator = User.query.get(creator_id)
        if not creator or creator.role != '家长':
            return {'code': 403, 'msg': '无权限操作'}
        
        new_content = Content(
            type=content_type,
            title=title,
            text=text,
            pinyin=pinyin,
            translation=translation,
            difficulty=difficulty,
            user_id=creator_id
        )
        db.session.add(new_content)
        db.session.commit()
        return {'code': 200, 'msg': '内容创建成功', 'content_id': new_content.content_id}

    @staticmethod
    def search_contents(keyword: str, page: int = 1, per_page: int = 10) -> dict:
        """分页搜索学习内容"""
        query = Content.query.filter(Content.title.ilike(f'%{keyword}%'))
        pagination = query.paginate(page=page, per_page=per_page)
        return {
            'code': 200,
            'data': [c.to_dict() for c in pagination.items],
            'total': pagination.total,
            'current_page': pagination.page
        }
    
    @staticmethod
    def get_contents(page=1, per_page=10):
        """分页查询学习内容"""
        try:
            # 计算偏移量
            offset = (page - 1) * per_page

            # 查询当前页数据
            contents = Content.query.offset(offset).limit(per_page).all()

            # 查询总条数
            total_count = Content.query.count()

            # 计算总页数
            total_pages = ceil(total_count / per_page)

            # 当前页记录数
            current_page_count = len(contents)

            # 

            # 格式化返回数据
            result = {
                'code': 200,
                'message': '查询成功',
                'data': {
                    'contents': [content.to_dict() for content in contents],
                    'pagination': {
                        'page': page,
                        'per_page': per_page,
                        'total_count': total_count,
                        'total_pages': total_pages,
                        'current_page_count': current_page_count
                    }
                }
            }
            db.session.commit()
            return result
        except Exception as e:
            db.session.rollback()
            return {
                'code': 500,
                'message': f'查询失败: {str(e)}',
                'data': None
            }
        
    @staticmethod
    def delete_content(content_id):
        content = Content.query.get(content_id)
        if not content:
            return {
                'status': 'not_found',  # 业务状态
                'code': 404,            # 业务状态码
                'message': '内容不存在',
                'data': None
            }
        
        db.session.delete(content)
        db.session.commit()
        return {
            'status': 'success',
            'code': 200,
            'message': '删除成功',
            'data': {'deleted_id': content_id}
        }
    @staticmethod
    def get_content_by_id(content_id: int) -> dict:
        content = Content.query.get(content_id)
        if not content:
            return {'code': 404, 'msg': '内容不存在'}
        return {'code': 200, 'msg': '内容查询成功', 'data': content.to_dict()}
    @staticmethod
    def update_content(content_id=None, **kwargs) -> dict:
        """通用保存方法：如果content_id存在则更新，不存在则新增"""
        print(f"正在{'更新' if content_id else '新增'}内容，参数: {kwargs}")  # 调试日志

        try:
            if content_id:
                # 更新现有内容
                content = Content.query.get(content_id)
                if not content:
                    return {'code': 404, 'msg': '内容不存在'}
                
                # 更新字段
                if 'title' in kwargs:
                    content.title = kwargs['title']
                if 'text' in kwargs:
                    content.text = kwargs['text']
                if 'pinyin' in kwargs:
                    content.pinyin = kwargs['pinyin']
                if 'translation' in kwargs:
                    content.translation = kwargs['translation']
                if 'difficulty' in kwargs:
                    content.difficulty = kwargs['difficulty']
                if 'type' in kwargs:
                    content.type = kwargs['type']
            else:
                # 新增内容
                content = Content(**kwargs)
                db.session.add(content)
            
            db.session.commit()
            return {'code': 200, 'msg': f'内容{"更新" if content_id else "新增"}成功'}
        except Exception as e:
            db.session.rollback()
            return {'code': 500, 'msg': f'内容保存失败: {str(e)}'}
    @staticmethod
    def get_contents_study(student_id=None, page=1, per_page=10):
        """分页查询未被任务使用的学习内容(学习内容管理)
        
        Args:
            student_id (int): 学生ID，查询未被该学生分配的内容
            page (int): 页码
            per_page (int): 每页数量
            
        Returns:
            dict: 包含查询结果和分页信息的字典
        """
        try:
            # 参数校验
            page = max(1, int(page))
            per_page = max(1, int(per_page))
            offset = (page - 1) * per_page

            # 构建基础SQL（使用参数化查询防止SQL注入）
            base_sql = """
                SELECT * FROM content 
                WHERE content_id NOT IN (
                    SELECT DISTINCT content_id FROM task 
                    WHERE content_id IS NOT NULL
                    {student_condition}
                )
            """.format(
                student_condition="AND student_id = :student_id" if student_id is not None else ""
            )

            # 分页查询
            paginated_sql = f"""
                {base_sql}
                ORDER BY content_id
                LIMIT :limit OFFSET :offset
            """
            
            # 准备参数
            params = {"limit": per_page, "offset": offset}
            if student_id is not None:
                params["student_id"] = student_id

            # 执行查询
            contents = db.session.execute(
                text(paginated_sql),
                params
            ).fetchall()

            # 总数查询
            count_sql = f"SELECT COUNT(*) FROM ({base_sql}) AS subquery"
            total_count = db.session.execute(
                text(count_sql),
                {"student_id": student_id} if student_id is not None else {}
            ).scalar()

            # 计算总页数
            total_pages = ceil(total_count / per_page) if total_count > 0 else 1

            # 转换结果
            contents_data = []
            for row in contents:
                if hasattr(row, 'to_dict'):  # 如果是ORM对象
                    contents_data.append(row.to_dict())
                else:  # 如果是原始Row对象
                    contents_data.append(dict(row._asdict()))

            return {
                "code": 200,
                "message": "查询成功",
                "data": {
                    "contents": contents_data,
                    "pagination": {
                        "page": page,
                        "per_page": per_page,
                        "total_count": total_count,
                        "total_pages": total_pages,
                        "current_page_count": len(contents_data)
                    }
                }
            }

        except Exception as e:
          
            return {
                "code": 500,
                "message": "查询失败，请稍后再试",
                "data": None
            }


    @staticmethod
    def generate_tasks(student_ids, content_ids):
        print(f"正在生成任务，学生ID: {student_ids}, 学习内容ID: {content_ids}")
        try:
            for content_id in content_ids:
                task = Task(
                    plan_date=datetime.now().strftime("%Y-%m-%d"),
                    task_type='每日', 
                    status=False,     
                    student_id=student_ids,
                    content_id=content_id,
                    review_stage=0    
                )
                db.session.add(task)
            db.session.commit()
            return {'code': 200, 'message': '任务生成成功'}
        except Exception as e:
            db.session.rollback()
            return {'code': 500, 'message': f'任务生成失败: {str(e)}'}